--Simple SELECT

SELECT	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D 


--Simple SELECT with Aliases

SELECT	STUD_LOC_CMP_CD as Campus_Location,
	STUD_ID as Student_Identification_Number,
	STUD_FST_NAM as First_Name,
	STUD_LST_NAM as Last_Name,
	STUD_DT_OF_BTH as Date_of_Birth,
	STUD_GNDR_CD as Gender_Code,
	STUD_GNDR_DESC as Gender_description,
	STUD_CUR_ACTV_FL as Current_Active_Flag
FROM 	STUD_BI.STUDENT_D

--Simple SELECT with Condition/Rules

SELECT 	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'

--Simple SELECT with Comparison Operators

SELECT 	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'
AND 	STUD_LOC_CMP_CD != '01'
AND 	STUD_GNDR_CD <> 'F'
AND 	STUD_DMSTC_FGN_CZ_STAT_CD = 'F'

--Simple SELECT with Compound Conditions

SELECT 	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'
AND 	STUD_LOC_CMP_CD = '01'
AND 	STUD_DMSTC_FGN_CZ_STAT_CD = 'F'

--Logical Operators
--Simple SELECT with And/Or Operators

SELECT 	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'
AND 	STUD_LOC_CMP_CD IN ('01', '04')
AND 	STUD_DMSTC_FGN_CZ_STAT_CD = 'F'
AND     ((STUD_GNDR_CD = 'M' OR STUD_IPEDS_GNDR_CD = 'M')
OR      (STUD_GNDR_IDNTY_CD = 'M' OR STUD_GNDR_AT_BTH_CD = 'M'))

--Simple SELECT with NOT Operator

SELECT 	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'
AND 	STUD_LOC_CMP_CD NOT IN ('01', '04', '06', '02', '07', '08')
AND 	STUD_DMSTC_FGN_CZ_STAT_CD = 'F'
AND     ((STUD_GNDR_CD = 'M' OR STUD_IPEDS_GNDR_CD = 'M')
OR      (STUD_GNDR_IDNTY_CD = 'M' OR STUD_GNDR_AT_BTH_CD = 'M'))


--Simple SELECT with IN Operator

SELECT STUD_LOC_CMP_CD,
STUD_ID,
STUD_FST_NAM,
STUD_LST_NAM,
STUD_DT_OF_BTH,
STUD_GNDR_CD,
STUD_GNDR_DESC,
STUD_CZ_STAT_CD,
STUD_CUR_ACTV_FL
FROM STUD_BI.STUDENT_D
WHERE STUD_CUR_ACTV_FL = 'Y'
AND STUD_LOC_CMP_CD = '01'
AND STUD_CZ_STAT_CD IN ('US', 'PR', 'RF', 'AM', 'AP', 'AS', 'DA', ' ')

--Simple SELECT with BETWEEN Operator

SELECT	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CZ_STAT_CD,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'
AND 	STUD_LOC_CMP_CD = '01'
AND 	STUD_DT_OF_BTH BETWEEN '01-08-1971' AND '01-08-1991'

--Simple SELECT with NOT BETWEEN Operator

SELECT	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CZ_STAT_CD,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'
AND 	STUD_LOC_CMP_CD = '01'
AND 	STUD_DT_OF_BTH NOT BETWEEN '01-08-1971' AND '01-08-1991'

--Simple SELECT with LIKE Operator (Example 1)

SELECT	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CZ_STAT_CD,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'
AND 	STUD_LOC_CMP_CD = '01'
AND 	STUD_LST_NAM LIKE ('DELM%') 

--Simple SELECT with LIKE Operator (Example 2)

SELECT	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CZ_STAT_CD,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'
AND 	STUD_LOC_CMP_CD = '01'
AND 	STUD_LST_NAM LIKE ('DELM___') 

--Simple SELECT with NOT LIKE Operator

SELECT	STUD_LOC_CMP_CD,
	STUD_ID,
	STUD_FST_NAM,
	STUD_LST_NAM,
	STUD_DT_OF_BTH,
	STUD_GNDR_CD,
	STUD_GNDR_DESC,
	STUD_CZ_STAT_CD,
	STUD_CUR_ACTV_FL
FROM 	STUD_BI.STUDENT_D
WHERE 	STUD_CUR_ACTV_FL = 'Y'
AND 	STUD_LOC_CMP_CD = '01'
AND 	STUD_LST_NAM NOT LIKE ('DELM%') 

--Simple SELECT with EXISTS Operator

SELECT CMP_LOC_LOC1_CD,
CMP_LOC_LOC1_SHRT_DESC,
CMP_LOC_LOC1_LNG_DESC,
CMP_LOC_LOC1_MXD_CASE_LNG_DESC,
CMP_LOC_LOC1_ABRV_DESC
FROM STUD_BI.CAMPUS_LOCATION_D
WHERE EXISTS (  SELECT  STUD_LOC_CMP_CD
                FROM    STUD_BI.STUDENT_D
                WHERE   STUD_CUR_ACTV_FL = 'Y'
                AND     STUD_LOC_CMP_CD IN ('01', '03', '05', '07', '09')
                AND     STUD_BI.CAMPUS_LOCATION_D.CMP_LOC_LOC1_CD = STUD_BI.STUDENT_D.STUD_LOC_CMP_CD)   
ORDER BY   CMP_LOC_LOC1_CD 

SELECT s.STUD_ID,
c.CMP_LOC_LOC1_CD,
c.CMP_LOC_LOC1_SHRT_DESC,
c.CMP_LOC_LOC1_MXD_CASE_LNG_DESC,
c.CMP_LOC_LOC1_ABRV_DESC
FROM STUD_BI.CAMPUS_LOCATION_D c,
STUD_BI.STUDENT_D s
WHERE c.CMP_LOC_LOC1_CD = s.STUD_LOC_CMP_CD
AND   s.STUD_LOC_CMP_CD IN ('01', '03', '05', '07', '09')       
                
--Simple SELECT with NOT EXISTS Operator

SELECT CMP_LOC_LOC1_CD,
CMP_LOC_LOC1_SHRT_DESC,
CMP_LOC_LOC1_LNG_DESC,
CMP_LOC_LOC1_MXD_CASE_LNG_DESC,
CMP_LOC_LOC1_ABRV_DESC
FROM STUD_BI.CAMPUS_LOCATION_D
WHERE NOT EXISTS (  SELECT  STUD_LOC_CMP_CD
                FROM    STUD_BI.STUDENT_D
                WHERE   STUD_CUR_ACTV_FL = 'Y'
                AND     STUD_LOC_CMP_CD IN ('01', '03', '05', '07', '09')
                AND     STUD_BI.CAMPUS_LOCATION_D.CMP_LOC_LOC1_CD = STUD_BI.STUDENT_D.STUD_LOC_CMP_CD)  
ORDER BY   CMP_LOC_LOC1_CD            
     

--Simple SELECT with GROUP BY

SELECT		STUD_LOC_CMP_CD,
		STUD_GNDR_DESC,
		STUD_DMSTC_FGN_CZ_STAT_CD,
		COUNT (DISTINCT STUD_ID) AS STUDENT_COUNT,
		MIN (STUD_DT_OF_BTH) MAX_DOB,
		MAX (STUD_DT_OF_BTH) MIN_DOB
FROM 		STUD_BI.STUDENT_D
WHERE 		STUD_CUR_ACTV_FL = 'Y'
GROUP BY  	STUD_LOC_CMP_CD,
		STUD_GNDR_DESC,
		STUD_DMSTC_FGN_CZ_STAT_CD

--Simple SELECT with ORDER BY

SELECT		STUD_LOC_CMP_CD,
		STUD_GNDR_DESC,
		STUD_DMSTC_FGN_CZ_STAT_CD,
		COUNT (DISTINCT STUD_ID) AS STUDENT_COUNT
FROM 		STUD_BI.STUDENT_D
WHERE 		STUD_CUR_ACTV_FL = 'Y'
GROUP BY  	STUD_LOC_CMP_CD,
		STUD_GNDR_DESC,
		STUD_DMSTC_FGN_CZ_STAT_CD
ORDER BY 	STUD_LOC_CMP_CD DESC

--Simple SELECT with HAVING

SELECT		STUD_LOC_CMP_CD,
		COUNT (DISTINCT STUD_ID) AS STUDENT_COUNT
FROM 		STUD_BI.STUDENT_D
WHERE 		STUD_CUR_ACTV_FL = 'Y'
GROUP BY  	STUD_LOC_CMP_CD
HAVING 		COUNT(DISTINCT STUD_ID) > 10000
ORDER BY 	STUD_LOC_CMP_CD ASC

--Simple SELECT with Concatenation

SELECT		STUD_LOC_CMP_CD||STUD_ID as Student_Identification_Number,
		STUD_LST_NAM || ','|| ' ' ||STUD_FST_NAM as Student_Name,
		STUD_DT_OF_BTH,
		STUD_GNDR_CD,
		STUD_GNDR_DESC,
		STUD_CZ_STAT_CD,
		STUD_CUR_ACTV_FL
FROM 		STUD_BI.STUDENT_D
WHERE 		STUD_CUR_ACTV_FL = 'Y'
AND 		STUD_LOC_CMP_CD = '01'
AND 		STUD_DT_OF_BTH BETWEEN '01-08-1971' AND '01-08-1991'

--Inner Join Example

SELECT 
        	t.ACAD_SUB_T_ACAD_YR,
        	t.ACAD_SUB_T_CD,
       		t.ACAD_SUB_T_NAM,
        	t.ACAD_SUB_T_REC_TY,
        	s.STUD_GNDR_DESC,
        	e.ENRL_STAT_NW_CR_REG_STAT_CD,
       		l.STUD_LVL_GRAD_UGRAD_CD,
                count (distinct STUD_ID)
FROM		STUD_BI.ENROLLMENT_F f
INNER JOIN 	STUD_BI.ACADEMIC_SUB_TERM_D t 
	  	on f.ACAD_SUB_T_KEY = t.ACAD_SUB_T_KEY
INNER JOIN 	STUD_BI.STUDENT_D s 
	  	on f.STUD_KEY = s.STUD_KEY
INNER JOIN 	STUD_BI.ENROLLMENT_STATUS_D e
          	on f.ENRL_STAT_KEY = e.ENRL_STAT_KEY
INNER JOIN 	STUD_BI.STUDENT_LEVEL_D l
          	on f.STUD_LVL_KEY = l.STUD_LVL_KEY
INNER JOIN      STUD_BI.CAMPUS_COLLEGE_MAJOR_CODE_D ccm
                on f.CMP_CG_MAJ_CD_KEY = ccm.CMP_CG_MAJ_CD_KEY
WHERE 		t.ACAD_SUB_T_ACAD_YR in (2015, 2016)
AND             f.ENRL_MAJ_CMPNT_NUM = 1
AND   		t.ACAD_SUB_T_CD = '2'
AND   		t.ACAD_SUB_T_REC_TY = '3WK'
AND             s.STUD_LOC_CMP_CD = '01'  
GROUP BY
        	t.ACAD_SUB_T_ACAD_YR,
        	t.ACAD_SUB_T_CD,
        	t.ACAD_SUB_T_NAM,
        	t.ACAD_SUB_T_REC_TY,
        	s.STUD_GNDR_DESC,
        	e.ENRL_STAT_NW_CR_REG_STAT_CD,
        	l.STUD_LVL_GRAD_UGRAD_CD
ORDER BY 
        		t.ACAD_SUB_T_ACAD_YR,
        		t.ACAD_SUB_T_CD,
        		t.ACAD_SUB_T_NAM,
        		t.ACAD_SUB_T_REC_TY,
        		s.STUD_GNDR_DESC,
        		e.ENRL_STAT_NW_CR_REG_STAT_CD,
        		l.STUD_LVL_GRAD_UGRAD_CD 

--Multiple Aggregate Functions within a Single SELECT Query

SELECT 
                s.STUD_LOC_CMP_CD,
        	t.ACAD_SUB_T_ACAD_YR,
        	t.ACAD_SUB_T_CD,
       		t.ACAD_SUB_T_NAM,
        	t.ACAD_SUB_T_REC_TY,
        	s.STUD_GNDR_DESC,
       		l.STUD_LVL_GRAD_UGRAD_CD,
                COUNT (DISTINCT STUD_ID) "STUDENT_COUNT",
                AVG(F.ENRL_CRED_BY_EXAM_AP_UNTS) "AVERAGE_AP_UNITS"
FROM		STUD_BI.ENROLLMENT_F f
INNER JOIN 	STUD_BI.ACADEMIC_SUB_TERM_D t 
	  	on f.ACAD_SUB_T_KEY = t.ACAD_SUB_T_KEY
INNER JOIN 	STUD_BI.STUDENT_D s 
	  	on f.STUD_KEY = s.STUD_KEY
INNER JOIN 	STUD_BI.ENROLLMENT_STATUS_D e
          	on f.ENRL_STAT_KEY = e.ENRL_STAT_KEY
INNER JOIN 	STUD_BI.STUDENT_LEVEL_D l
          	on f.STUD_LVL_KEY = l.STUD_LVL_KEY
INNER JOIN      STUD_BI.CAMPUS_COLLEGE_MAJOR_CODE_D ccm
                on f.CMP_CG_MAJ_CD_KEY = ccm.CMP_CG_MAJ_CD_KEY
WHERE 		t.ACAD_SUB_T_ACAD_YR IN (2016)
AND             f.ENRL_MAJ_CMPNT_NUM = 1
AND   		t.ACAD_SUB_T_CD = '2'
AND   		t.ACAD_SUB_T_REC_TY = '3WK'
AND             l.STUD_LVL_GRAD_UGRAD_CD = 'U'
GROUP BY
        	s.STUD_LOC_CMP_CD,
        	t.ACAD_SUB_T_ACAD_YR,
        	t.ACAD_SUB_T_CD,
        	t.ACAD_SUB_T_NAM,
        	t.ACAD_SUB_T_REC_TY,
        	s.STUD_GNDR_DESC,
        	l.STUD_LVL_GRAD_UGRAD_CD
ORDER BY 
                        s.STUD_LOC_CMP_CD,
        		t.ACAD_SUB_T_ACAD_YR,
        		t.ACAD_SUB_T_CD,
        		t.ACAD_SUB_T_NAM,
        		t.ACAD_SUB_T_REC_TY,
        		s.STUD_GNDR_DESC,
        		l.STUD_LVL_GRAD_UGRAD_CD